<?php

namespace app\services\data\stat\ad\normal;

use app\model\sdk\FirstupAd;
use app\services\data\stat\ad\StatBase;

class StatBaseReportFirstup extends StatBase
{
    // 买量统计通常关联mysql的plan和media_user表
    // 方便抓取维度数据和条件判断
    protected function queryJoin($table_name = 'firstups')
    {
        $sql = "
            LEFT JOIN(
                SELECT id AS plan_id, group_id AS plan_group_id, owner_id AS plan_owner_id, media_id, media_docking_id FROM
                mysql('{$this->sdkHost}', 'sdk', 'ad_plan', 'sdk', '{$this->sdkPwd}')
            ) AS ad_plan
            ON {$table_name}.plan_id = ad_plan.plan_id
            LEFT JOIN(
                SELECT id AS media_user_id, daili_id FROM
                mysql('{$this->sdkHost}', 'sdk', 'ad_media_user', 'sdk', '{$this->sdkPwd}')
            ) AS ad_media_user
            ON {$table_name}.media_user_id = ad_media_user.media_user_id
        ";

        return $sql;
    }

    public function queryWhere($keys, $table_name = 'firstups')
    {
        $wheres = [];
        foreach ($keys as $key) {
            switch ($key) {
                // 所属游戏
                case 'game_id':
                    if (!empty($this->gameIds)) {
                        $ids = implode(',', $this->gameIds);
                        $wheres[] = "{$table_name}.game_id IN ({$ids})";
                    }
                    break;
                // 所属渠道
                case 'channel':
                    if (!empty($this->channels)) {
                        $ids = implode(',', $this->channels);
                        $wheres[] = "{$table_name}.channel IN ({$ids})";
                    }
                    break;
                // 所属渠道应用
                case 'game_channel_id':
                    if (!empty($this->gameChannelIds)) {
                        $ids = implode(',', $this->gameChannelIds);
                        $wheres[] = "{$table_name}.game_channel_id IN ({$ids})";
                    }
                    break;
                // 所属媒体
                case 'media_id':
                    if (!empty($this->mediaIds)) {
                        $ids = implode(',', $this->mediaIds);
                        $wheres[] = "{$table_name}.media_id IN ({$ids})";
                    }
                    break;
                // 所属媒体对接类型
                case 'media_docking':
                    if (!empty($this->mediaDockingIds)) {
                        $ids = implode(',', $this->mediaDockingIds);
                        $wheres[] = "{$table_name}.media_docking_id IN ({$ids})";
                    }
                    break;
                // 用户来源
                case 'source_type':
                    if ($this->sourceType > 0) {
                        if ($this->sourceType == FirstupAd::SOURCE_NORMAL) {
                            $wheres[] = "{$table_name}.plan_id = 0";
                        } else {
                            $wheres[] = "{$table_name}.plan_id > 1";
                        }
                    }
                    break;
                // 设备类型
                case 'device_type':
                    if (!empty($this->deviceTypes)) {
                        $ids = implode(',', $this->deviceTypes);
                        $wheres[] = "{$table_name}.device_type IN ({$ids})";
                    }
                    break;
                // 投放人
                case 'owner':
                    if (!empty($this->ownerIds)) {
                        $ids = implode(',', $this->ownerIds);
                        $wheres[] = "{$table_name}.plan_owner_id IN ({$ids})";
                    }
                    break;
                // 代理
                case 'daili':
                    if (!empty($this->dailiIds)) {
                        $ids = implode(',', $this->dailiIds);
                        $wheres[] = "{$table_name}.daili_id IN ({$ids})";
                    }
                    break;
                // 计划组
                case 'plan_group_id':
                    if (!empty($this->planGroupIds)) {
                        $ids = implode(',', $this->planGroupIds);
                        $wheres[] = "{$table_name}.plan_group_id IN ({$ids})";
                    }
                    break;
                // 计划id
                case 'plan_id':
                    if (!empty($this->planIds)) {
                        $ids = implode(',', $this->planIds);
                        $wheres[] = "{$table_name}.plan_id IN ({$ids})";
                    }
                    break;
                // 媒体账号
                case 'media_user_id':
                    if (!empty($this->mediaUserIds)) {
                        $ids = implode(',', $this->mediaUserIds);
                        $wheres[] = "{$table_name}.media_user_id IN ({$ids})";
                    }
                    break;
                // 时间范围
                case 'time':
                    $wheres[] = "{$table_name}.`#create_time` >= {$this->stime}";
                    $wheres[] = "{$table_name}.`#create_time` < {$this->etime}";
                    break;
                // 创建时间不能为空
                case 'create_time':
                    $wheres[] = "{$table_name}.`#create_time` > 1";
                    break;
            }

        }

        $str = '1=1';
        if (!empty($wheres)) {
            $str = implode(' AND ', $wheres);
        }

        return $str;
    }

    protected function queryGroupBy($table_name = 'firstups')
    {
        foreach ($this->groupbys as $name) {
            switch ($name) {
                case 'stat_hour':
                    $selects[] = "formatDateTime(toDateTime({$table_name}.`#create_time`), '%H', '{$this->timezone}') AS stat_hour";
                    break;
                case 'stat_date':
                    $selects[] = "toDate({$table_name}.`#create_time`, '{$this->timezone}') AS stat_date";
                    break;
                case 'stat_week':
                    $selects[] = "formatDateTime(toDateTime({$table_name}.`#create_time`), '%Y-%V', '{$this->timezone}') AS stat_week";
                    break;
                case 'stat_month':
                    $selects[] = "formatDateTime(toDateTime({$table_name}.`#create_time`), '%Y-%m', '{$this->timezone}') AS stat_month";
                    break;
                case 'media_id':
                case 'plan_id':
                case 'plan_group_id':
                case 'plan_owner_id':
                    $selects[] = "ad_plan.{$name} AS {$name}";
                    break;
                case 'daili_id':
                    $selects[] = "ad_media_user.{$name} AS {$name}";
                    break;
                case 'adgroup_id':
                    $selects[] = "{$table_name}.myad_group_name AS {$name}";
                    break;
                case 'ad_id':
                    $selects[] = "{$table_name}.myad_ad_name AS {$name}";
                    break;
                case 'creative_id':
                    $selects[] = "{$table_name}.myad_creative_name AS {$name}";
                    break;
                default:
                    $selects[] = "{$table_name}.{$name} AS {$name}";
                    break;
            }
        }

        $select_str  = implode(',', $selects);
        $groupby_str = implode(',', $this->groupbys);

        return [$select_str, $groupby_str];
    }
}
